
import java.io.IOException;
import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Set;
import java.util.TreeSet;

public class DBImport {
	private static DBImport instance = null;
	// Logger
	private logger myLogger;
	//
	private boolean loggerReady;
	
	private void writeToLog(int level,String message){
		if (loggerReady) myLogger.log(level,message);
	}
	
	
	/**
	 * constructor
	 * @throws SQLException 
	 * @throws ClassNotFoundException 
	 * @throws IllegalAccessException 
	 * @throws InstantiationException 
	 */
	DBImport(boolean flag,logger myLogger) throws InstantiationException, IllegalAccessException, ClassNotFoundException, SQLException {
		// We already know a connection pool has been made
		this.myLogger=myLogger;
		this.loggerReady=flag;
		
	}
	
	
	/**
	 * This is a singleton class
	 */
	public synchronized static DBImport getInstance(boolean flag,logger myLogger) throws InstantiationException, IllegalAccessException, ClassNotFoundException, SQLException {
		if (instance == null) {
			instance = new DBImport(flag,myLogger);
		}
		return instance;
	}

	
	/**
	 * This function is the main function to add a data file to the database
	 */
	public int addToDatabase(String filename, Connection conn) throws SQLException {
		Map<Object,Integer> artistsMap = new HashMap<Object,Integer>();
		Map<Object,Integer> genresMap = new HashMap<Object,Integer>();
		Map<Object,Integer>	updatedDiscsMap;
		Set<Long> updatedDiscsSet;
		
		if (conn == null)
				return 0;
		try{
				//opening a new transaction
				conn.setAutoCommit(false);
					     
				//creating a new iterator to iterate over the discs file
				Disc_Iter iter=new Disc_Iter(filename);
				Disc_Iter iter2=new Disc_Iter(filename);
				Disc_Iter iter3=new Disc_Iter(filename);
				Disc_Iter iter4=new Disc_Iter(filename);
				Disc_Iter iter5=new Disc_Iter(filename);
				Disc_Iter iter6=new Disc_Iter(filename);
				Disc_Iter iter7=new Disc_Iter(filename);
			
				this.addArtists(iter,conn);
				this.writeToLog(1,"finished artists");
				this.addGenres(iter2,conn);
				this.writeToLog(1,"finished genres");
				
				artistsMap=DBOperations.selectToMap("ARTISTS", conn);
				genresMap=DBOperations.selectToMap("GENRES", conn);
				
				updatedDiscsSet=this.addDiscs(iter3, artistsMap, genresMap,conn);
				this.writeToLog(1,"finished discs");
				this.addSongs(updatedDiscsSet,iter4,conn);
				this.writeToLog(1,"finished songs");		
				updatedDiscsMap=this.updateDiscs(iter5, artistsMap, genresMap,conn);
				this.writeToLog(1,"finished updating discs");
				this.updateSongs(iter6,iter7, updatedDiscsMap,conn);
				
				//if everything was ok, we commit the transaction
				conn.commit();
				return 1;
		}
		//if we caught an excpetion- we rollback!
		catch (IOException e)
		{
			if (conn!=null) conn.rollback();
			this.writeToLog(2, "can not read from file");
			this.writeToLog(2, e.getMessage());
			return 0;
		}
		catch (SQLException e){
			if (conn!=null) conn.rollback();
			this.writeToLog(2,"an error occured while using the DB" );
			this.writeToLog(2, e.getMessage());
			return 0;
		}
		catch (Exception e){
			if (conn!=null) conn.rollback();
			this.writeToLog(2,"an error occured" );
			this.writeToLog(2, e.getMessage());			
			return 0;
		}		

	}
	
	//this function takes a string disc string from the data file and parses it to a Disc object
	private Disc parseDisc(String discStr){
		Disc returnDisc = new Disc();
		String song;
		String titleStr;String ID,revision;
		List<String> discSongs = new ArrayList<String>();
		int beginindex, endindex=0;
		
		if (discStr==null) return null;
		
		try //added to make sure we have an artist+title+DiscID
		{
			try	// parsing the revision. no revision---> revision=0;
			{
				beginindex=discStr.indexOf("Revision")+10;
				endindex=discStr.indexOf("\n", beginindex);
				revision=discStr.substring(beginindex, endindex);
				returnDisc.setRevision(Integer.parseInt(revision));
			}
			catch (Exception e){returnDisc.setRevision(0);};
			
			//we get the DiscID from the File
			beginindex=discStr.indexOf("DISCID")+7;
			endindex=discStr.indexOf("\n", beginindex);
			ID=discStr.substring(beginindex, endindex);
		
			//this part of the code transforms the hex key to long
			ID=strValidation.verifyDiscID(ID);
			returnDisc.setID(Long.parseLong(ID, 16));
			
			//we get the Disc title from the file		
			beginindex=discStr.indexOf("DTITLE")+7;
			endindex=discStr.indexOf("\n", beginindex);
		
			titleStr=discStr.substring(beginindex, endindex);
			
			//in case the artist name and the title are the same
			if (titleStr.indexOf(" / ")==-1) {
				returnDisc.setTitle(titleStr);
				returnDisc.setArtist(titleStr);
			}
			//in case the artist name and title are not the same
			else {
				returnDisc.setArtist(titleStr.substring(0, titleStr.indexOf(" / ")));

				//if the artist name and title name are in the same row
				if (titleStr.substring(titleStr.indexOf(" / ")+3).length()>1){
					returnDisc.setTitle(titleStr.substring(titleStr.indexOf(" / ")+3));
				}
				// if the artist name and title name are in different rows
				else
				{
					if(discStr.indexOf("DTITLE", endindex)!=-1){
						beginindex=discStr.indexOf("DTITLE",endindex)+7;
						endindex=discStr.indexOf("\n", beginindex);
						titleStr=discStr.substring(beginindex, endindex);
						returnDisc.setTitle(titleStr);
					}
					//if we can not find a proper title, we abort
					else
					{
						return null;
					}
					
				}
			}
		}
		catch (Exception e){ //in case we couldn't get the Disc ID/title/artist name
			return null;
		}
		beginindex=discStr.indexOf("DYEAR", endindex)+6;
		endindex=discStr.indexOf("\n", beginindex);
		
		try {	//parses the Year. no Year---> year=0;
			returnDisc.setYear(Integer.parseInt(discStr.substring(beginindex, endindex)));
		}
		//if we can't get a year, set the year to 0
		catch (Exception e){
			returnDisc.setYear(0);
		}
		
		beginindex=discStr.indexOf("DGENRE", endindex)+7;
		endindex=discStr.indexOf("\n", beginindex);
		
		//in case we don't have a genre
		try {
			if (discStr.substring(beginindex, endindex).length()>1)
			{
				returnDisc.setGenre(discStr.substring(beginindex, endindex));
			}
			else returnDisc.setGenre("UnKnown");
			}
		catch (Exception e){
			returnDisc.setGenre("UnKnown");
		}
		
		// Looking for the first song
		beginindex=discStr.indexOf("TTITLE", endindex);
		endindex=beginindex;
		
		//iterate as long as we have songs
		while (beginindex!=-1) {
			beginindex=discStr.indexOf("=", endindex)+1;
			endindex=discStr.indexOf("\n", beginindex);
			song=discStr.substring(beginindex, endindex);
			discSongs.add(song);
			
			beginindex=discStr.indexOf("TTITLE", endindex);
			if (beginindex==-1) endindex=beginindex;
		}
		returnDisc.setSongs(discSongs);
		
		//getting the parent Genre from the file (was entred manually)
		beginindex=discStr.indexOf("PARENT_GENRE")+13;
		endindex=discStr.indexOf("/", beginindex);
		String parentGenre = discStr.substring(beginindex, endindex);
		returnDisc.setParentGenre(parentGenre);
		
		return returnDisc;
		
	}
	
	//this function create the artists table from the data file
	private void addArtists(Disc_Iter iter,Connection conn) throws SQLException{
		//get the current artists Set(unique) from the DB
		Set<Object> currentArtists= DBOperations.selectToSet("ARTISTS", conn);
		String currentDiscString;
		Disc currentDisc;
		PreparedStatement pstmt = null;
		String sql;
		int count=0;
		
		sql = "insert into ARTISTS (NAME) values (?)";
		pstmt = conn.prepareStatement(sql);
		
		// while we have another disc in the list, iterate
		while (iter.hasNext()) {
			//get the next disc (represented as string).
			currentDiscString=iter.next();
			// check that all chars are ASCII
			if (strValidation.testAscii(currentDiscString))
			{
					currentDisc=parseDisc(currentDiscString);
				// make sure our "new" artists is not in the DB
				if ((currentDisc!=null)&&(!currentArtists.contains(currentDisc.getArtist()))){
					//update the SET
					currentArtists.add(currentDisc.getArtist());
					//update the DB
					pstmt.setString(1, currentDisc.getArtist());
					pstmt.addBatch();
					count++;
					if (count % 3000==0) {
						pstmt.executeBatch();
						count=0;
						
					}		
				}
			}
		}
		pstmt.executeBatch();
						
	}
	
	//this function creates the genres table from the data file
	private void addGenres(Disc_Iter iter,Connection conn) throws SQLException{
		//get the current genres from the DB
		Set<Object> currentGenres= DBOperations.selectToSet("Genres", conn);
		//get the parent genres names' and ID's from DB
		Map<Object,Integer> ParentGenresMap=DBOperations.selectToMap("PARENT_GENRES",conn);
		String currentDiscString;
		Disc currentDisc;
		PreparedStatement pstmt = null;
		String sql;
		int count=0;
			
		sql = "insert into GENRES (GENRE_NAME,PARENT_GENRE_ID) values (?,?)";
		pstmt = conn.prepareStatement(sql);
		
		// while we have another disc in the file, iterate
		while (iter.hasNext()) {
			//get the Disc in string format
			currentDiscString=iter.next();
			//check that we only have ASCII chars
			if (strValidation.testAscii(currentDiscString))
			{
				currentDisc=parseDisc(currentDiscString);
				//check that we don't have the "new" genre in the DB
				if ((currentDisc!=null)&&(!currentGenres.contains(currentDisc.getGenre()))){
					//update the genres Set
					currentGenres.add(currentDisc.getGenre());
					//update the DB
					pstmt.setString(1, currentDisc.getGenre());
					pstmt.setInt(2, ParentGenresMap.get(currentDisc.getParentGenre()));
					pstmt.addBatch();
					count++;
					if (count % 3000==0) {
						pstmt.executeBatch();
						count=0;
						
					}				
				}
			}
		}
		pstmt.executeBatch();		
	}

	//this function creates the discs table from the file
	private Set<Long> addDiscs(Disc_Iter iter, Map<Object,Integer> artistsMap,
			Map<Object,Integer> genresMap,Connection conn) throws SQLException{
		
		//get the current discs from the DB
		Set<Object> currentDiscs= DBOperations.selectToSet("DISCS", conn); //discs that are currently in the table
		Set<Long> updatedDiscs= new TreeSet<Long>();	// for discs that we add in this iteration
		String currentDiscString;
		Disc currentDisc;
		PreparedStatement pstmt = null;
		String sql;
		int count=0;

		sql = "insert into DISCS (ARTISTS_ID,YEAR,GENRE_ID,TITLE,CD_ID,REVISION) values (?,?,?,?,?,?)";
		pstmt = conn.prepareStatement(sql);
		
		//as long as we have discs in the file, iterate
		while (iter.hasNext()) {
			//get the next disc in string format
			currentDiscString=iter.next();
			//check that we only have ASCII chars
			if (strValidation.testAscii(currentDiscString))
			{
			currentDisc=parseDisc(currentDiscString);
				//check that we don't have that CDID in the DB
				if ((currentDisc!=null)&& (!currentDiscs.contains(currentDisc.getID()))){
						//update the current discs set
						currentDiscs.add(currentDisc.getID());
						// update the new discs set (needed for songs)
						updatedDiscs.add(currentDisc.getID());
						//update the DB
						pstmt.setInt(1, artistsMap.get(currentDisc.getArtist()));
						pstmt.setInt(2, currentDisc.getYear());
						pstmt.setInt(3,genresMap.get(currentDisc.getGenre()));
						pstmt.setString(4,currentDisc.getTitle());
						pstmt.setLong(5,currentDisc.getID());
						pstmt.setInt(6, currentDisc.getRevision());			
						pstmt.addBatch();
						count++;
							if (count % 1000==0) {
								pstmt.executeBatch();
								count=0;
							}
				}
			}
		}
		
		pstmt.executeBatch();
		return updatedDiscs;
		
	}
	
	//this function creates the songs table from the file
	private void addSongs(Set<Long> updatedDiscs,Disc_Iter iter,Connection conn) throws SQLException{
		//create a new Set to hold the discs we are populating with songs
		Set<Long> addedDiscs=new TreeSet<Long>();
		PreparedStatement pstmt = null;
		String sql,currentDiscString;
		Disc currentDisc;
		int count=0;

		sql = "insert into Songs (TRACK_ID,SONG_NAME,CD_ID) values (?,?,?)";
		pstmt = conn.prepareStatement(sql);
			
			// while we have another disc in the file, iterate
			while (iter.hasNext()) 
			{
				//get the next CD in a string format
				currentDiscString=iter.next();
				//check that we only have ASCII chars
				if (strValidation.testAscii(currentDiscString))
				{
					currentDisc=parseDisc(currentDiscString);
					/*check that:
					  1. this cd was inserted to Discs table in the previous iteration
					  2. this cd was NOT inserted to Songs table in this iteration
					 */
					if ((currentDisc!=null)&&(updatedDiscs.contains(currentDisc.getID()))&& (!addedDiscs.contains(currentDisc.getID())))
					{
						int trackNum=0;
						// update the songs in the DB
						for (String song: currentDisc.getSongs())
						{
							trackNum++;
							pstmt.setLong(3, currentDisc.getID());
							pstmt.setString(2, song.toLowerCase());
							pstmt.setInt(1,trackNum);
							pstmt.addBatch();
							count++;
							if (count % 3000==0) {
								pstmt.executeBatch();
								count=0;
							}
						
						}
						// add this CD to set, so we can enforce item #2 above
						addedDiscs.add(currentDisc.getID());
					}
				}			
			}
			pstmt.executeBatch();	
	}
		
	
	//this function updates the discs table according to revision
	private Map<Object,Integer> updateDiscs(Disc_Iter iter, Map<Object,Integer> artistsMap,
			Map<Object,Integer> genresMap,Connection conn) throws SQLException{

		// get the current Discs from the DB
		Map<Object,Integer> currentDiscs= DBOperations.selectToMap("Discs",conn); //discs that are currently in the table
		Map<Object,Integer> updatedDiscs= new HashMap<Object,Integer>(); //the discs that we are updating
		String currentDiscString;
		Disc currentDisc;
		PreparedStatement pstmt = null;
		String sql;
		int count=0;
		  
		sql= "UPDATE DISCS SET ARTISTS_ID=?,YEAR=?,GENRE_ID=?,TITLE=?,REVISION=?" +"WHERE CD_ID=?"/*+"values (?,?,?,?,?,?)"*/; 
		pstmt = conn.prepareStatement(sql);
		
		//while we have another disc in the file, iterate
		while (iter.hasNext()) {
			//the the next cd in string format
			currentDiscString=iter.next();
			//check that the CD has only ASCII chars
			if (strValidation.testAscii(currentDiscString))
			{
				currentDisc=parseDisc(currentDiscString);
				
				//this means we have to update the record- higher revision
				if ((currentDisc!=null)&& (currentDiscs.containsKey(currentDisc.getID()))
						&&(currentDiscs.get(currentDisc.getID())<currentDisc.getRevision())){
						//update the discs set
						updatedDiscs.put(currentDisc.getID(), currentDisc.getRevision());
						//update the DB
						pstmt.setInt(1, artistsMap.get(currentDisc.getArtist()));
						pstmt.setInt(2, currentDisc.getYear());
						pstmt.setInt(3,genresMap.get(currentDisc.getGenre()));
						pstmt.setString(4,currentDisc.getTitle());
						pstmt.setLong(6,currentDisc.getID());
						pstmt.setInt(5, currentDisc.getRevision());			
	
						pstmt.addBatch();
						count++;
							if (count % 1000==0) {
								pstmt.executeBatch();
								count=0;
							}
				}
			}
		}
		
		pstmt.executeBatch();
		return updatedDiscs;
		
	}

	//this function updates the songs table according to revision
	private void updateSongs(Disc_Iter iter,Disc_Iter iter2,Map<Object,Integer> updatedDiscs,Connection conn) throws SQLException{
		//this set will hold the CD's that are updated in this iteration
		Set<Long> addedDiscs=new TreeSet<Long>();
		String currentDiscString;
		Disc currentDisc;
		PreparedStatement pstmt = null;
		String sql;
		int count=0;
		
			sql= "DELETE FROM SONGS WHERE CD_ID=?";
			pstmt = conn.prepareStatement(sql);
			
			/* impotant: we are using Delete+Insert.
			 * reason: let's same old revision had 5 songs.
			 * new revision had 6 songs.
			 * an update operation will fail here because song#6 should be inserted, not updated.
			 */
		
		//as long as we have a new CD in the file, iterate
		while (iter.hasNext()) {
			//get the next disc in string format
			currentDiscString=iter.next();
			//check that this disc has only ASCII chars
			if (strValidation.testAscii(currentDiscString))
			{
				currentDisc=parseDisc(currentDiscString);
			
			//this means we have to update the record- the CD revision matches the MAP
				if ((currentDisc!=null)&&(updatedDiscs.containsKey(currentDisc.getID()))
						&&(updatedDiscs.get(currentDisc.getID())==currentDisc.getRevision())){		
					//update the DB
					pstmt.setLong(1,currentDisc.getID());
					pstmt.addBatch();
					count++;
						if (count % 3000==0) {
							pstmt.executeBatch();
							count=0;
						}
				}
			}
		}
		
		pstmt.executeBatch();
		
		sql = "insert into Songs (TRACK_ID,SONG_NAME,CD_ID) values (?,?,?)";
		pstmt = conn.prepareStatement(sql);
		
		//while we have another disc, iterate
		while (iter2.hasNext()) {
			//get the next cd in string format
			currentDiscString=iter2.next();
			//check that the cd has only ASCII chars
			if (strValidation.testAscii(currentDiscString))
			{
				currentDisc=parseDisc(currentDiscString);
				
				//this means we have to update the record- the CD revision matches the MAP
				if ((currentDisc!=null)&&(updatedDiscs.containsKey(currentDisc.getID()))
						&&(updatedDiscs.get(currentDisc.getID())==currentDisc.getRevision())&&(!addedDiscs.contains(currentDisc.getID()))){
				
					int trackNum=0;
					//iterate the songs and update the DB
					for (String song: currentDisc.getSongs())
					{				
						trackNum++;
						pstmt.setLong(3, currentDisc.getID());
						pstmt.setString(2, song.toLowerCase());
						pstmt.setInt(1,trackNum);
						pstmt.addBatch();
						count++;
						if (count % 3000==0) {
							pstmt.executeBatch();
							count=0;
						}
					}
					//update the set
					addedDiscs.add(currentDisc.getID());
				}
			}
		}
		pstmt.executeBatch();
	}
	

	
}
